//Documentation complete
/**
 * @author Alex Peterson
 * @version 2009FE22
 */

package broker;

import problemDomain.*;
import static problemDomain.ErrorBean.throwError;
import static problemDomain.ErrorBean.ErrorCode.*;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * <code>Broker</code> for <code>PatronAccountBean</code>.
 */
public class PatronAccountBroker extends Broker<PatronAccountBean> {
	
	/* Singleton Constructor */
	
	private static PatronAccountBroker broker;
	
	/**
	 * Default constructor. Calls the openConnection method.
	 * @throws ErrorBean
	 */
	private PatronAccountBroker() throws ErrorBean {}
	
	/**
	 * Retrieves the static PatronAccountBroker instance.
	 * @return static PatronAccountBroker
	 * @throws ErrorBean
	 */
	public static PatronAccountBroker getBroker () throws ErrorBean {
		if (broker == null)
			broker = new PatronAccountBroker();
		return broker;
	}
	
	/* END Singleton Constructor */
	
	
	/* Prepared Statements */
	
	private PreparedStatement insertSQL;
	private PreparedStatement getInsertSQL () throws ErrorBean {
		return prepare(insertSQL, "INSERT INTO patron SET first_name = ?, last_name = ?, phone = ?;");
	}
	
	private PreparedStatement updateSQL;
	private PreparedStatement getUpdateSQL () throws ErrorBean {
		return prepare(updateSQL, "UPDATE patron SET first_name = ?, last_name = ?, phone = ? WHERE patron_id = ?;");
	}
	
	private PreparedStatement deletePatronSQL;
	private PreparedStatement getDeletePatronSQL () throws ErrorBean {
		return prepare(deletePatronSQL, "CALL sp_delete_patron(?)");
	}

	/* END Prepared Statements */
	
	
	/* (Broker) Methods */
	
	@Override
	public boolean persist (PatronAccountBean pab) throws ErrorBean {				
		pab.isValid();
		
		int id = pab.getId();
		String first_name = pab.getFirstname();
		String last_name = pab.getLastname();
		String phone = pab.getPhone();
		
		if (id == 0) {
			try {
				PreparedStatement insertSQL = getInsertSQL();
				insertSQL.setString(1, first_name);
				insertSQL.setString(2, last_name);
				insertSQL.setString(3, phone);
				insertSQL.execute();
				return true;
			} catch(SQLException e) {
				throwError(
					BROKER_INSERT_ERROR,
					e.getMessage(),
					this);
			}
		} else if (id > 0) {
			try {
				PreparedStatement updateSQL = getUpdateSQL();
				updateSQL.setString(1, first_name);
				updateSQL.setString(2, last_name);
				updateSQL.setString(3, phone);
				updateSQL.setInt(4, id);
				updateSQL.execute();
				return true;
			} catch(SQLException e) {
				throwError(
					BROKER_UPDATE_ERROR,
					e.getMessage(),
					this);
			}
		}		
		return false;
	}

	@Override
	public boolean remove (PatronAccountBean pab) throws ErrorBean {
		int id = pab.getId();
		
		try {
			PreparedStatement deletePatronSQL = getDeletePatronSQL();
			deletePatronSQL.setInt(1, id);
			deletePatronSQL.execute();			
			return true;
		} catch(SQLException e) {
			throwError(
				BROKER_DELETE_ERROR,
				e.getMessage(),
				this);
			return false;
		}
	}
	
	@Override
	public List<PatronAccountBean> search (PatronAccountBean searchBean) throws ErrorBean {
		List<PatronAccountBean> search = new ArrayList<PatronAccountBean>();
		PatronAccountBean pab;
		
		boolean isFirst = true;
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT * FROM patron");
		
		//Search by Patron ID
		int id = searchBean.getId();
		if (id > 0) {
			sql.append(appendClause("patron_id = " + id, isFirst));
			isFirst = false;
		}
		else {
			//Search by Patron first name
			String first_name = searchBean.getFirstname();
			if (!"".equals(first_name)) {
				sql.append(appendClause("first_name LIKE '%" + first_name + "%'", isFirst));
				isFirst = false;
			}
			
			//Search by Patron last name
			String last_name = searchBean.getLastname();
			if (!"".equals(last_name)) {
				sql.append(appendClause("last_name LIKE '%" + last_name + "%'", isFirst));
				isFirst = false;
			}
			
			//Search by Patron phone number
			String phone = searchBean.getPhone();
			if (!"".equals(phone)) {
				sql.append(appendClause("phone LIKE '%" + phone + "%'", isFirst));
				isFirst = false;
			}
		}
		
		sql.append(';');
		
		try	{
			Statement stmt = conn.createStatement();
			if (!sql.toString().equals("")) {
				ResultSet results = stmt.executeQuery(sql.toString());
				
				while (results.next()) {
					pab = new PatronAccountBean();
					pab.setId(results.getInt("patron_id"));
					pab.setFirstname(results.getString("first_name"));
					pab.setLastname(results.getString("last_name"));
					pab.setPhone(results.getString("phone"));
					
					search.add(pab);
				}
			}
		} catch (SQLException e) {
			throwError(
				BROKER_SELECT_ERROR,
				e.getMessage(),
				this);
		}
		return search;
	}
	
	@Override
	public List<PatronAccountBean> listAll () throws ErrorBean {
		List<PatronAccountBean> search = new ArrayList<PatronAccountBean>();
		PatronAccountBean pab;

		StringBuilder sql = new StringBuilder("SELECT * FROM patron;");
		
		try	{
			Statement stmt = conn.createStatement();
			if (!sql.toString().equals("")) {
				ResultSet results = stmt.executeQuery(sql.toString());
				
				while (results.next()) {
					pab = new PatronAccountBean();
					pab.setId(results.getInt("patron_id"));
					pab.setFirstname(results.getString("first_name"));
					pab.setLastname(results.getString("last_name"));
					pab.setPhone(results.getString("phone"));

					search.add(pab);
				}
			}
		} catch (SQLException e) {
			throwError(
				BROKER_SELECT_ERROR,
				e.getMessage(),
				this);
		}
		return search;
	}
	
	/* END (Broker) Methods */
	
}
